
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.MallInterface.MallGoodsFreight;

namespace BCData.TMS.MallInterface.MallGoodsFreight
{
    public class MallGoodsFreightData : IMallGoodsFreightData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    A.MallGoodsFreightId,
		    A.MallShopNo,
		    A.GoodsSource,
		    A.TransportBusinessType,
		    A.FreightType,
		    A.GoodsSku,
		    A.GoodsName,
		    A.GoodsWeight,
		    A.GoodsVolume,
		    A.GoodsLength,
		    A.GoodsWidth,
		    A.GoodsHeight,
		    A.GoodsCount,
		    A.GoodsPrice,
		    A.GoodsUnit,
		    A.IsMonthPay,
		    A.StartPrice,
		    A.StartMileage,
		    A.EveryMileagePrice,
		    A.ProvinceCode,
		    A.ProvinceName,
		    A.CityCode,
		    A.CityName,
		    A.CountyCode,
		    A.CountyName,
		    A.CreateTime,
		    A.UpdateTime
        FROM
            MallGoodsFreight A";



        #region 构造函数
        public MallGoodsFreightData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        public async Task<MallGoodsFreightEntity> InsertAsync(MallGoodsFreightEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        #region updateAsync
        public async Task<MallGoodsFreightEntity> UpdateAsync(MallGoodsFreightEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        public async Task<bool> DeleteAsync(long mallGoodsFreightId)
        {
            string sql = "DELETE FROM mallgoodsfreight WHERE MallGoodsFreightId = ?mallGoodsFreightId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MallGoodsFreightId", mallGoodsFreightId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        public async Task<MallGoodsFreightEntity> GetEntityAsync(long mallGoodsFreightId)
        {
            string sql = QUERY + " WHERE  A.MallGoodsFreightId = ?mallGoodsFreightId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MallGoodsFreightId", mallGoodsFreightId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<MallGoodsFreightEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询商城商品运费记录
        public async Task<(IEnumerable<MallGoodsFreightEntity>, int)> GetListAsync(string mallShopNo, string goodsSource, string transportBusinessType, string freightType, string goodsSku, string goodsName, decimal? goodsWeight, decimal? goodsVolume, decimal? goodsLength, decimal? goodsWidth, decimal? goodsHeight, int? goodsCount, decimal? goodsPrice, string goodsUnit, bool? isMonthPay, decimal? startPrice, int? startMileage, decimal? everyMileagePrice, string provinceCode, string cityCode, string countyCode, int pageIndex, int pageSize)
        { 
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(mallShopNo))
            {
                wheres.Add("A.MallShopNo=?MallShopNo");
                parameters.Add("?MallShopNo", mallShopNo, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(goodsSource))
            {
                wheres.Add("A.GoodsSource=?GoodsSource");
                parameters.Add("?GoodsSource", goodsSource, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(transportBusinessType))
            {
                wheres.Add("A.TransportBusinessType=?TransportBusinessType");
                parameters.Add("?TransportBusinessType", transportBusinessType, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(freightType))
            {
                wheres.Add("A.FreightType=?FreightType");
                parameters.Add("?FreightType", freightType, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(goodsSku))
            {
                wheres.Add("A.GoodsSku=?GoodsSku");
                parameters.Add("?GoodsSku", goodsSku, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(goodsName))
            {
                wheres.Add("A.GoodsName=?GoodsName");
                parameters.Add("?GoodsName", goodsName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(goodsUnit))
            {
                wheres.Add("A.GoodsUnit=?GoodsUnit");
                parameters.Add("?GoodsUnit", goodsUnit, DbType.AnsiString);
            }

            if (goodsWeight.HasValue)
            {
                wheres.Add("A.GoodsWeight = ?GoodsWeight");
                parameters.Add("?GoodsWeight", goodsWeight.Value, DbType.Decimal);
            }
            if (goodsVolume.HasValue)
            {
                wheres.Add("A.GoodsVolume = ?GoodsVolume");
                parameters.Add("?GoodsVolume", goodsVolume.Value, DbType.Decimal);
            }
            if (goodsLength.HasValue)
            {
                wheres.Add("A.GoodsLength = ?GoodsLength");
                parameters.Add("?GoodsLength", goodsLength.Value, DbType.Decimal);
            }
            if (goodsWidth.HasValue)
            {
                wheres.Add("A.GoodsWidth = ?GoodsWidth");
                parameters.Add("?GoodsWidth", goodsWidth.Value, DbType.Decimal);
            }
            if (goodsHeight.HasValue)
            {
                wheres.Add("A.GoodsHeight = ?GoodsHeight");
                parameters.Add("?GoodsHeight", goodsHeight.Value, DbType.Decimal);
            }
            if (goodsCount.HasValue)
            {
                wheres.Add("A.GoodsCount = ?GoodsCount");
                parameters.Add("?GoodsCount", goodsCount.Value, DbType.Decimal);
            }
            if (goodsPrice.HasValue)
            {
                wheres.Add("A.GoodsPrice = ?GoodsPrice");
                parameters.Add("?GoodsPrice", goodsPrice.Value, DbType.Decimal);
            }
            if (isMonthPay.HasValue)
            {
                wheres.Add("A.IsMonthPay = ?IsMonthPay");
                parameters.Add("?IsMonthPay", isMonthPay.Value, DbType.Decimal);
            }
            if (startPrice.HasValue)
            {
                wheres.Add("A.StartPrice = ?StartPrice");
                parameters.Add("?StartPrice", startPrice.Value, DbType.Decimal);
            }
            if (startMileage.HasValue)
            {
                wheres.Add("A.StartMileage = ?StartMileage");
                parameters.Add("?StartMileage", startMileage.Value, DbType.Decimal);
            }
            if (everyMileagePrice.HasValue)
            {
                wheres.Add("A.EveryMileagePrice = ?EveryMileagePrice");
                parameters.Add("?EveryMileagePrice", everyMileagePrice.Value, DbType.Decimal);
            } 


            string sql = string.Format(@"{0} {1} LIMIT {2},{3} ; SELECT COUNT(1) FROM  mallgoodsfreight  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize
                  );

            return await this.database.Slave.QueryPageableListAsync<MallGoodsFreightEntity>(sql, parameters);
        }
        #endregion

    }
}
        //接口
        
        
        
